﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IBM.Data.DB2;
using System.Collections.Specialized;
using CourseManager.Domain.Entities;

namespace CourseManager.Domain.Repositories
{
    public class CourseRepository
    {
        DatabaseTransac DatabaseTans;
        public IEnumerable<Course> getCourse(int pId)
        {
            Course Course = new Course();
            string Sql = "Select id,description,Duration,InstructorId,startDate,endDate,Name "
                          +"from {0}.Course where id = {1};";
            Sql = string.Format(Sql,Properties.Settings.Default.Schema, pId);
            
            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Course
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Description = dr[1].ToString(),
                              Duration =Convert.ToInt32(dr[2]),
                              InstructorId = Convert.ToInt32(dr[3]),
                              StartDate = Convert.ToDateTime(dr[4]),
                              EndDate = Convert.ToDateTime(dr[5]),
                              Name = Convert.ToString(dr[6])

                          }).OrderBy(i => i.Id);

            return Result.AsEnumerable();
        }

        public Course getCourseById(int pId)
        {
            Course Course = new Course();
            string Sql = "Select id,description,Duration,InstructorId,startDate,endDate,Name "
                          + "from {0}.Course where id = {1};";
            Sql = string.Format(Sql, Properties.Settings.Default.Schema, pId);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Course
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Description = dr[1].ToString(),
                              Duration = Convert.ToInt32(dr[2]),
                              InstructorId = Convert.ToInt32(dr[3]),
                              StartDate = Convert.ToDateTime(dr[4]),
                              EndDate = Convert.ToDateTime(dr[5]),
                              Name = Convert.ToString(dr[6])

                          }).OrderBy(i => i.Id);

            return Result.First();
        }


        public IEnumerable<Course> getCourse(string pName)
        {
            Course Course = new Course();
            string Sql = "select id,description,Duration,InstructorId,startDate,endDate,Name "
                        + "from {0}.Course where Name like '%{1}%'; ";
            Sql = string.Format(Sql,Properties.Settings.Default.Schema, pName);
            
            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Course
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Description = dr[1].ToString(),
                              Duration = Convert.ToInt32(dr[2]),
                              InstructorId = Convert.ToInt32(dr[3]),
                              StartDate = Convert.ToDateTime(dr[4]),
                              EndDate = Convert.ToDateTime(dr[5]),
                              Name = Convert.ToString(dr[6])

                          }).OrderBy(i => i.Name);

            return Result.AsEnumerable();
        }

        public void AddCourse(Course pCourse)
        {
            NameValueCollection ValuesToInsert = new NameValueCollection();
            
            ValuesToInsert.Add("Name", "'"+pCourse.Name.ToString()+"'");
            ValuesToInsert.Add("description","'"+pCourse.Description+"'");
            ValuesToInsert.Add("Duration", pCourse.Duration.ToString());
            ValuesToInsert.Add("InstructorId", pCourse.InstructorId.ToString());
            ValuesToInsert.Add("StartDate", "'" + pCourse.StartDate.ToString("yyyy-MM-dd hh:mm:ss") + "'");
            ValuesToInsert.Add("EndDate", "'" + pCourse.EndDate.ToString("yyyy-MM-dd hh:mm:ss")+"'");

            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeInsert(Properties.Settings.Default.Schema + ".Course", ValuesToInsert);

        }

        public void UpdateCourse(Course pCourse)
        {
            NameValueCollection ValuesToInsert = new NameValueCollection();

            ValuesToInsert.Add("Name", "'" + pCourse.Name.ToString() + "'");
            ValuesToInsert.Add("description", "'" + pCourse.Description+ "'");
            ValuesToInsert.Add("Duration", pCourse.Duration.ToString());
            ValuesToInsert.Add("InstructorId", pCourse.InstructorId.ToString());
            ValuesToInsert.Add("StartDate", "'" + pCourse.StartDate.ToString("yyyy-MM-dd hh:mm:ss") + "'");
            ValuesToInsert.Add("EndDate", "'" + pCourse.EndDate.ToString("yyyy-MM-dd hh:mm:ss") + "'");

            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeUpdate(Properties.Settings.Default.Schema + ".Course", ValuesToInsert, new List<string> { "Id= " + pCourse.Id });
        
        }

        public void DeleteCourse(int pId)
        {
            try
            {
                DatabaseTans = new DatabaseTransac();
                DatabaseTans.executeDelete(Properties.Settings.Default.Schema + ".Course", new List<string> { "Id = " + pId });

            }
            catch (Exception)
            {
                
                throw;
            }
        
        }
    }
}
